package com.zr.dao.impl;

import com.mysql.jdbc.StringUtils;
import com.zr.dao.UserDao;
import com.zr.utils.DBUtil;
import com.zr.vo.User;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class UserDaoImpl extends DBUtil implements UserDao {

    @Override
    public User login(String userId, String password) {
        User user = null;
        String sql = "select * from t_user where userid = ? and password = ?";
        try{
            ResultSet rs = query(sql,new Object[]{userId, password});
            if(rs.next()){
                user = new User();
                setValueFromResultSet(user, rs);
            }
        }catch (SQLException e){
            e.printStackTrace();
        }finally {
            closeConnection();
        }
        return user;
    }

    @Override
    public List<User> list(User user) {
        List<User> userList = new ArrayList<>();
        try {
            StringBuffer sb = getWhere(new StringBuffer("select * from t_user where 1 = 1 "), user.getUserId(), user.getUserName());
            sb.append("limit ? , ?");
            ResultSet rs = query(sb.toString(), new Object[]{(user.getPageNum()-1)*user.getPageSize(),user.getPageSize()});
            while (rs.next()){
                User u = new User();
                setValueFromResultSet(u, rs);
                userList.add(u);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return userList;
    }

    @Override
    public int count(User user) {
        int total = 0;
        try {
            StringBuffer sb = new StringBuffer("select count(0) as total from t_user where 1 = 1 ");
            ResultSet rs = query(getWhere(sb, user.getUserId(), user.getUserName()).toString(), null);
            if (rs.next()){
               total = rs.getInt("total");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return total;
    }

    @Override
    public int remove(String userId) throws SQLException {
        int count = 0;
        String sql = "delete from t_user where userid = ?";
        try{
            count =  modify(sql,new Object[]{userId});
            commit();
        }catch (SQLException e){
            rollback();
            throw e;
        }finally {
            closeConnection();
        }
        return  count;
    }

    @Override
    public int add(User user) throws SQLException {
        int count = 0;
        String sql = "insert into t_user values (?,?,?,?,?,?,?)";
        try{
            count =  modify(sql,new Object[]{user.getUserId(), user.getUserName(), user.getPassword(),user.getSex(),user.getTel(), user.getStatus(), user.getImg()});
            commit();
        }catch (SQLException e){
            rollback();
            throw e;
        }finally {
            closeConnection();
        }
        return  count;
    }

    @Override
    public int modify(User user) throws SQLException {
        int count = 0;
        String sql = "update t_user set username=?,password=?,sex=?,tel=?,status=?,img=? where userid=?";
        try{
            count =  modify(sql,new Object[]{user.getUserName(), user.getPassword(),user.getSex(),user.getTel(), user.getStatus(), user.getImg(),user.getUserId()});
            commit();
        }catch (SQLException e){
            rollback();
            throw e;
        }finally {
            closeConnection();
        }
        return  count;
    }

    private StringBuffer getWhere(StringBuffer sb, String userId, String userName){
        if(!StringUtils.isNullOrEmpty(userId)){
            sb.append("and userid like '%" + userId + "%' ");
        }
        if(!StringUtils.isNullOrEmpty(userName)){
            sb.append("and username like '%" + userName + "%' ");
        }
        return sb;
    }
    private void setValueFromResultSet(User user, ResultSet rs) throws SQLException {
        user.setUserId(rs.getString("userid"));
        user.setUserName(rs.getString("username"));
        user.setPassword(rs.getString("password"));
        user.setSex(rs.getString("sex"));
        user.setTel(rs.getString("tel"));
        user.setStatus(rs.getInt("status"));
        user.setImg(rs.getString("img"));
    }
}
